package ddbs;

import java.util.LinkedList;
import java.util.List;
import java.util.Hashtable;

public class SQLParse {
    static PeerMgr pm;

    static int publisher[] = { 0, 1, 2, 3 };
    static int book[] = { 0, 1, 2 };
    static int customer[] = { 0, 1 };
    static int order[] = { 0, 1, 2, 3 };
    static Hashtable<String, Integer> ht = new Hashtable<String, Integer>();
    static String tableName[] = { "Publisher", "Book", "Customer", "Orders" };

    public static List<LocalWork> parse(PeerMgr pm, String sql) {
        System.out.println("Parse sql: " + sql);

        SQLParse.pm = pm;
        int start, end = 0;
        int fm, fn = 0;
        String fields, alllimit, table = null;
        String templimit[], limit[] = null;
        List<String> bin = new LinkedList<String>();
        String cur, temp = null;
        List<LocalWork> workList = new LinkedList<LocalWork>();
        Work w;
        ht.put("Publisher", publisher.length);
        ht.put("Book", book.length);
        ht.put("Customer", 1);
        ht.put("Orders", order.length);
        String tempsql = new String();

        if (sql.indexOf("where") != -1) {
            tempsql = "select "
                    + sql.substring(sql.indexOf("select") + 6,
                            sql.indexOf("from")).trim()
                    + " from "
                    + sql.substring(sql.indexOf("from") + 4,
                            sql.indexOf("where")).trim() + " where";
        }

        start = sql.indexOf("select");
        start = start + 7;
        end = sql.indexOf("from");
        fields = sql.substring(start, end - 1);
        fields = fields.trim();
        start = end + 5;
        if ((end = sql.indexOf("where")) != -1) {
            table = sql.substring(start, end - 1);
            table = table.trim();
            start = end + 6;
            alllimit = sql.substring(start);
            templimit = alllimit.split("and");
            int binnum = 0;
            int limitnum = 0;
            // limit = new String[templimit.length];
            if (templimit != null && templimit.length > 0) {
                for (int i = 0; i != templimit.length; i++) {
                    if (sql.indexOf("where") != -1) {
                        if (i != 0)
                            tempsql += " and";
                        tempsql += " " + templimit[i].trim();
                    }
                    int count = 0;
                    for (int num = 0; num != 4; num++) {
                        if (templimit[i].contains(tableName[num])) {
                            count++;
                        }
                    }
                    if (count == 2) {
                        bin.add(templimit[i].trim());
                        binnum++;
                    } else {
                        // limit[limitnum]=templimit[i];
                        limitnum++;
                    }
                }
            }
            if (limitnum != 0) {
                limit = new String[limitnum];
                limitnum = 0;
                if (templimit != null && templimit.length > 0) {
                    for (int i = 0; i != templimit.length; i++) {
                        int count = 0;
                        for (int num = 0; num != 4; num++) {
                            if (templimit[i].contains(tableName[num])) {
                                count++;
                            }
                        }
                        if (count != 2) {
                            limit[limitnum] = templimit[i].trim();
                            limitnum++;
                        }
                    }
                }
            }
        } else {
            table = sql.substring(start);
            table = table.trim();
            limit = null;
            bin = null;
        }

        if (bin == null || bin.isEmpty()) {
            do {
                start = table.indexOf(",");
                String tempf[] = fields.split(",");
                if (start != -1)
                    cur = table.substring(0, start);
                else
                    cur = table;
                for (int num = 0; num != 4; num++) {
                    if (cur.contains(tableName[num])) {
                        if (num == 2) {
                            workList.add(getCustomer(fields, table, bin, limit,
                                    tempsql, null,0));
                            return workList;
                        }
                        cur = tableName[num];
                        for (int i = 0; i != ht.get(cur); i++) {
                            w = new Work();
                            w.ds = new DataSet();
                            w.generateId();
                            w.ds.peer = pm.getPeer(i);
                            // w.imsql=sql;
                            fn = 0;
                            fm = 0;
                            int filedslength = 0;
                            while (fn != tempf.length) {
                                // if (tempf[fn].contains(cur)){
                                filedslength++;
                                // }
                                fn++;
                            }
                            fn = 0;
                            // System.out.println(filedslength);
                            w.ds.fileds = new String[filedslength];
                            while (fn != tempf.length) {
                                // if (tempf[fn].contains(cur)){
                                w.ds.fileds[fm] = tempf[fn].trim();
                                fm++;
                                // }
                                fn++;
                            }
                            w.ds.table = cur;
                            int wlimit = 0;
                            if (limit != null && limit.length > 0) {
                                int limitlength = 0;
                                for (int j = 0; j != limit.length; j++) {
                                    // if((limit[j].contains(cur)||(!limit[j].contains(".")))&&(limit[j]!=null)){
                                    limitlength++;
                                    // }
                                }
                                w.ds.limit = new String[limitlength];
                                for (int j = 0; j != limit.length; j++) {
                                    // if((limit[j].contains(cur)||(!limit[j].contains(".")))&&(limit[j]!=null)){
                                    w.ds.limit[wlimit] = limit[j].trim();
                                    wlimit++;
                                    // }
                                }
                            }
                            w.imsql = w.formalSQL();
                            workList.add(w);
                        }
                    }
                }
                if (start != -1)
                    temp = table.substring(start + 1);
                else
                    temp = null;
                table = temp;
            } while (table != null);
        } else {
            workList = getbin(fields, table, bin, limit, tempsql, null);
        }
        return workList;
    }

    public static List<LocalWork> getbin(String fileds, String table,
            List<String> bin, String limit[], String sql, String exfileds) {
        if (bin.size() == 1) {
            List<LocalWork> bw = new LinkedList<LocalWork>();
            BinWork leafbw;
            LocalWork w1, w2;
            String cur = bin.get(0);
            String cur1 = null, cur2 = null;
            String joinBy[];
            String exf[] = cur.split("=");

            int filedslength = 0, limitlength = 0, exfiledslength = 0;

            for (int i = 0; i != 2; i++) {
                if (!fileds.contains(exf[i])) {
                    if (exfileds == null)
                        exfileds = exf[i];
                    else
                        exfileds = exfileds + "," + exf[i];
                }
            }
            int num = 0;
            int fm, fn, exfm, exfn;
            for (num = 0; num != 4; num++) {
                if (cur.contains(tableName[num])) {
                    cur1 = tableName[num];
                    break;
                }
            }
            num++;
            for (; num != 4; num++) {
                if (cur.contains(tableName[num])) {
                    cur2 = tableName[num];
                    break;
                }
            }
            String tempf[] = fileds.split(",");
            String extempf[] = null;
            if (exfileds != null) {
                extempf = exfileds.split(",");
            }
            for (int i = 0; i != ht.get(cur1); i++) {
                if (cur1.contains("Customer")) {
                    StringBuffer cfileds, cexfileds;
                    cfileds = new StringBuffer();
                    cexfileds = new StringBuffer();
                    String climit[] = null;
                    for (int cus = 0; cus != tempf.length; cus++) {
                        if (tempf[cus].contains("Customer")) {
                            if (cus == 0) {
                                cfileds.append(tempf[cus]);
                            } else {
                                cfileds.append("," + tempf[cus]);
                            }
                        }
                    }
                    if (exfileds != null) {
                        for (int cus = 0; cus != extempf.length; cus++) {
                            if (extempf[cus].contains("Customer")) {
                                if (cus == 0) {
                                    cexfileds.append(extempf[cus]);
                                } else {
                                    cexfileds.append("," + extempf[cus]);
                                }
                            }
                        }
                    }
                    if (limit != null) {
                        int climitnum = 0;
                        for (int cus = 0; cus != limit.length; cus++) {
                            if (limit[cus].contains("Customer")) {
                                climitnum++;
                            }
                        }
                        climit = new String[climitnum];
                        climitnum = 0;
                        for (int cus = 0; cus != limit.length; cus++) {
                            if (limit[cus].contains("Customer")) {
                                climit[climitnum] = limit[cus];
                                climitnum++;
                            }
                        }
                    }
                    w1 = getCustomer(cfileds.toString(), "Customer", null,
                            climit, sql, cexfileds.toString(),1);
                } else {
                    w1 = new Work();
                    w1.ds = new DataSet();
                    w1.generateId();
                    w1.ds.peer = pm.getPeer(i);
                    fn = 0;
                    fm = 0;
                    filedslength = 0;
                    exfiledslength = 0;
                    limitlength = 0;
                    while (fn != tempf.length) {
                        if (tempf[fn].contains(cur1)) {
                            filedslength++;
                        }
                        fn++;
                    }
                    fn = 0;
                    w1.ds.fileds = new String[filedslength];
                    while (fn != tempf.length) {
                        if (tempf[fn].contains(cur1)) {
                            w1.ds.fileds[fm] = tempf[fn].trim();
                            fm++;
                        }
                        fn++;
                    }

                    if (extempf != null) {
                        exfm = 0;
                        exfn = 0;
                        while (exfn != extempf.length) {
                            if (extempf[exfn].contains(cur1)) {
                                exfiledslength++;
                            }
                            exfn++;
                        }
                        exfn = 0;
                        w1.ds.exfileds = new String[exfiledslength];
                        while (exfn != extempf.length) {
                            if (extempf[exfn].contains(cur1)) {
                                w1.ds.exfileds[exfm] = extempf[exfn].trim();
                                exfm++;
                            }
                            exfn++;
                        }
                    }

                    w1.ds.table = cur1;
                    int wlimit = 0;
                    if (limit != null && limit.length > 0) {
                        for (int j = 0; j != limit.length; j++) {
                            if (limit[j].contains(cur1) && limit[j] != null) {
                                limitlength++;
                            }
                        }
                        w1.ds.limit = new String[limitlength];
                        for (int j = 0; j != limit.length; j++) {
                            if (limit[j].contains(cur1) && limit[j] != null) {
                                w1.ds.limit[wlimit] = limit[j].trim();
                                wlimit++;
                            }
                        }
                    }
                }
                for (int p = 0; p != ht.get(cur2); p++) {
                    if (cur2.contains("Customer")) {
                        StringBuffer cfileds, cexfileds;
                        cfileds = new StringBuffer();
                        cexfileds = new StringBuffer();
                        String climit[] = null;
                        for (int cus = 0; cus != tempf.length; cus++) {
                            if (tempf[cus].contains("Customer")) {
                                if (cus == 0) {
                                    cfileds.append(tempf[cus]);
                                } else {
                                    cfileds.append("," + tempf[cus]);
                                }
                            }
                        }
                        if (exfileds != null) {
                            for (int cus = 0; cus != extempf.length; cus++) {
                                if (extempf[cus].contains("Customer")) {
                                    if (cus == 0) {
                                        cexfileds.append(extempf[cus]);
                                    } else {
                                        cexfileds.append("," + extempf[cus]);
                                    }
                                }
                            }
                        }
                        if (limit != null) {
                            int climitnum = 0;
                            for (int cus = 0; cus != limit.length; cus++) {
                                if (limit[cus].contains("Customer")) {
                                    climitnum++;
                                }
                            }
                            climit = new String[climitnum];
                            climitnum = 0;
                            for (int cus = 0; cus != limit.length; cus++) {
                                if (limit[cus].contains("Customer")) {
                                    climit[climitnum] = limit[cus];
                                    climitnum++;
                                }
                            }
                        }
                        w2 = getCustomer(cfileds.toString(), "Customer", null,
                                climit, sql, cexfileds.toString(),1);
                    } else {
                        w2 = new Work();
                        w2.ds = new DataSet();
                        w2.generateId();
                        w2.ds.peer = pm.getPeer(p);
                        fn = 0;
                        fm = 0;
                        exfn = 0;
                        filedslength = 0;
                        exfiledslength = 0;
                        while (fn != tempf.length) {
                            if (tempf[fn].contains(cur2)) {
                                filedslength++;
                            }
                            fn++;
                        }
                        fn = 0;
                        w2.ds.fileds = new String[filedslength];
                        while (fn != tempf.length) {
                            if (tempf[fn].contains(cur2)) {
                                w2.ds.fileds[fm] = tempf[fn].trim();
                                fm++;
                            }
                            fn++;
                        }

                        if (extempf != null) {
                            exfm = 0;
                            exfn = 0;
                            while (exfn != extempf.length) {
                                if (extempf[exfn].contains(cur2)) {
                                    exfiledslength++;
                                }
                                exfn++;
                            }
                            exfn = 0;
                            w2.ds.exfileds = new String[exfiledslength];
                            while (exfn != extempf.length) {
                                if (extempf[exfn].contains(cur2)) {
                                    w2.ds.exfileds[exfm] = extempf[exfn].trim();
                                    exfm++;
                                }
                                exfn++;
                            }
                        }

                        w2.ds.table = cur2;
                        int w2limit = 0;
                        if (limit != null && limit.length > 0) {
                            limitlength = 0;
                            for (int q = 0; q != limit.length; q++) {
                                if (limit[q].contains(cur2) && limit[q] != null) {
                                    limitlength++;
                                }
                            }
                            w2.ds.limit = new String[limitlength];
                            for (int q = 0; q != limit.length; q++) {
                                if (limit[q].contains(cur2) && limit[q] != null) {
                                    w2.ds.limit[w2limit] = limit[q].trim();
                                    w2limit++;
                                }
                            }
                        }
                    }
                    leafbw = new BinWork();
                    leafbw.generateId();
                    leafbw.w1 = new Work();
                    leafbw.w1 = w1;
                    leafbw.w1.ds = w1.ds;
                    leafbw.w2 = new Work();
                    leafbw.w2 = w2;
                    leafbw.w2.ds = w2.ds;
                    leafbw.ds = new DataSet();
                    leafbw.ds.fileds = new String[w1.ds.fileds.length
                            + w2.ds.fileds.length];
                    for (int m = 0; m != w1.ds.fileds.length; m++) {
                        leafbw.ds.fileds[m] = w1.ds.fileds[m];
                    }
                    for (int n = 0; n != w2.ds.fileds.length; n++) {
                        leafbw.ds.fileds[n + w1.ds.fileds.length] = w2.ds.fileds[n];
                    }
                    leafbw.ds.table = w1.ds.table + "," + w2.ds.table;
                    limitlength = 0;
                    if (w1.ds.limit != null) {
                        limitlength = limitlength + w1.ds.limit.length;
                    }
                    if (w2.ds.limit != null) {
                        limitlength = limitlength + w2.ds.limit.length;
                    }
                    if (limitlength != 0) {
                        leafbw.ds.limit = new String[limitlength];
                        for (int m = 0; m != w1.ds.limit.length; m++) {
                            leafbw.ds.limit[m] = w1.ds.limit[m];
                        }
                        for (int n = 0; n != w2.ds.limit.length; n++) {
                            leafbw.ds.limit[n + w1.ds.limit.length] = w2.ds.limit[n];
                        }
                    }

                    exfiledslength = 0;
                    if (w1.ds.exfileds != null) {
                        exfiledslength = exfiledslength + w1.ds.exfileds.length;
                    }
                    if (w2.ds.exfileds != null) {
                        exfiledslength = exfiledslength + w2.ds.exfileds.length;
                    }
                    if (exfiledslength != 0) {
                        leafbw.ds.exfileds = new String[exfiledslength];
                        for (int m = 0; m != w1.ds.exfileds.length; m++) {
                            leafbw.ds.exfileds[m] = w1.ds.exfileds[m];
                        }
                        for (int n = 0; n != w2.ds.exfileds.length; n++) {
                            leafbw.ds.exfileds[n + w1.ds.exfileds.length] = w2.ds.exfileds[n];
                        }
                    }

                    joinBy = cur.split("=");
                    if (joinBy[0].contains(cur1)){
                        leafbw.joinBy[0] = joinBy[0];
                        leafbw.joinBy[1] = joinBy[1];
                    }
                    else{
                        leafbw.joinBy[0] = joinBy[1];
                        leafbw.joinBy[1] = joinBy[0];
                    }
                    String jo = joinBy[0].substring(joinBy[0].indexOf(".") + 1);
                    String jo1 = joinBy[1]
                            .substring(joinBy[1].indexOf(".") + 1);
                    if (jo.length() < jo1.length())
                        leafbw.joinBy[2] = jo;
                    else
                        leafbw.joinBy[2] = jo1;
                    // System.out.println(leafbw.w2.ds.fileds.length);
                    leafbw.imsql = leafbw.formalSQL();
                    leafbw.w1.setImsql(w1.formalSQL());
                    leafbw.w2.setImsql(w2.formalSQL());
                    bw.add(leafbw);
                    // System.out.println(bw.get(0).STATUS);
                }
            }
            return bw;
        } else {
            List<LocalWork> bw = new LinkedList<LocalWork>();
            String cur1 = bin.get(0), cur2 = bin.get(1);
            String joinBy[] = new String[3];
            int num;
            String name = null;
            for (num = 0; num != 4; num++) {
                if (cur1.contains(tableName[num])
                        && cur2.contains(tableName[num])) {
                    name = tableName[num];
                    break;
                }
            }
            /*
             * if(name==null){ List<String> lfbin=new LinkedList<String>();
             * lfbin.add(bin.get(0)); bin.remove(0); List<LocalWork>
             * lw=getbin(fileds,table,lfbin,limit,sql); List<LocalWork>
             * rw=getbin(fileds,table,bin,limit,sql); while (!lw.isEmpty()){
             * while(!rw.isEmpty()){ BinWork leafbw=new BinWork();
             * leafbw.imsql=sql; leafbw.generateId(); leafbw.ds=new DataSet();
             * leafbw.w1=new BinWork(); leafbw.w1=lw.get(0);
             * leafbw.w1.ds=lw.get(0).ds; leafbw.w2=new BinWork();
             * leafbw.w2=rw.get(0); leafbw.w2.ds=rw.get(0).ds; leafbw.ds=new
             * DataSet(); leafbw.ds.fileds=new
             * String[leafbw.w1.ds.fileds.length+leafbw.w2.ds.fileds.length];
             * for (int m=0;m!=leafbw.w1.ds.fileds.length;m++){
             * leafbw.ds.fileds[m]=leafbw.w1.ds.fileds[m]; } for (int
             * n=0;n!=leafbw.w2.ds.fileds.length;n++){
             * leafbw.ds.fileds[n+leafbw.
             * w1.ds.fileds.length]=leafbw.w2.ds.fileds[n]; }
             * leafbw.ds.table=leafbw.w1.ds.table+leafbw.w2.ds.table; int
             * limitlength = 0; if (leafbw.w1.ds.limit != null){ limitlength =
             * limitlength + leafbw.w1.ds.limit.length; } if (leafbw.w2.ds.limit
             * != null){ limitlength = limitlength + leafbw.w2.ds.limit.length;
             * } if (limitlength != 0){ leafbw.ds.limit=new String[limitlength];
             * for (int m=0;m!=leafbw.w1.ds.limit.length;m++){
             * leafbw.ds.limit[m]=leafbw.w1.ds.limit[m]; } for (int
             * n=0;n!=leafbw.w2.ds.limit.length;n++){
             * leafbw.ds.limit[n+leafbw.w1
             * .ds.limit.length]=leafbw.w2.ds.limit[n]; } } bw.add(leafbw);
             * rw.remove(0); } lw.remove(0); } } else{
             */
            String othername = null;
            for (num = 0; num != 4; num++) {
                if ((cur1.contains(tableName[num])) && (tableName[num] != name)) {
                    othername = tableName[num];
                    break;
                }
            }
            String bin1 = bin.get(0);
            bin.remove(0);
            String addfileds[] = bin1.split("=");
            for (int i = 0; i != 2; i++) {
                if ((addfileds[i].contains(name))
                        && (!fileds.contains(addfileds[i]))) {
                    if (exfileds == null)
                        exfileds = addfileds[i].trim();
                    else
                        exfileds = exfileds + "," + addfileds[i].trim();
                    break;
                }
            }
            List<LocalWork> lw = getbin(fileds, table, bin, limit, sql,
                    exfileds);
            for (int i = 0; i != 2; i++) {
                if ((addfileds[i].contains(othername))
                        && (!fileds.contains(addfileds[i]))) {
                    if (exfileds == null)
                        exfileds = addfileds[i];
                    else
                        exfileds = exfileds + "," + addfileds[i].trim();
                    break;
                }
            }
            String tempf[] = fileds.split(",");
            String extempf[] = null;
            if (exfileds != null) {
                extempf = exfileds.split(",");
            }
            while (!lw.isEmpty()) {
                for (int i = 0; i != ht.get(othername); i++) {
                    BinWork leafbw = new BinWork();
                    leafbw.ds = new DataSet();
                    leafbw.generateId();
                    LocalWork w2;
                    int filedslength = 0, exfiledslength = 0;
                    if (othername.contains("Customer")) {
                        StringBuffer cfileds, cexfileds;
                        cfileds = new StringBuffer();
                        cexfileds = new StringBuffer();
                        String climit[] = null;
                        for (int cus = 0; cus != tempf.length; cus++) {
                            if (tempf[cus].contains("Customer")) {
                                if (cus == 0) {
                                    cfileds.append(tempf[cus]);
                                } else {
                                    cfileds.append("," + tempf[cus]);
                                }
                            }
                        }
                        if (exfileds != null) {
                            for (int cus = 0; cus != extempf.length; cus++) {
                                if (extempf[cus].contains("Customer")) {
                                    if (cus == 0) {
                                        cexfileds.append(extempf[cus]);
                                    } else {
                                        cexfileds.append("," + extempf[cus]);
                                    }
                                }
                            }
                        }
                        if (limit != null) {
                            int climitnum = 0;
                            for (int cus = 0; cus != limit.length; cus++) {
                                if (limit[cus].contains("Customer")) {
                                    climitnum++;
                                }
                            }
                            climit = new String[climitnum];
                            climitnum = 0;
                            for (int cus = 0; cus != limit.length; cus++) {
                                if (limit[cus].contains("Customer")) {
                                    climit[climitnum] = limit[cus];
                                    climitnum++;
                                }
                            }
                        }
                        w2 = getCustomer(cfileds.toString(), "Customer", null,
                                climit, sql, cexfileds.toString(),1);
                    } else {
                        w2 = new Work();
                        w2.generateId();
                        w2.ds = new DataSet();
                        w2.ds.peer = pm.getPeer(i);
                        int fn = 0;
                        int fm = 0;
                        int exfm, exfn;
                        filedslength = 0;
                        exfiledslength = 0;
                        while (fn != tempf.length) {
                            if (tempf[fn].contains(othername)) {
                                filedslength++;
                            }
                            fn++;
                        }
                        fn = 0;
                        // System.out.println(filedslength);
                        w2.ds.fileds = new String[filedslength];
                        while (fn != tempf.length) {
                            if (tempf[fn].contains(othername)) {
                                w2.ds.fileds[fm] = tempf[fn].trim();
                                fm++;
                            }
                            fn++;
                        }

                        if (extempf != null) {
                            exfm = 0;
                            exfn = 0;
                            while (exfn != extempf.length) {
                                if (extempf[exfn].contains(othername)) {
                                    exfiledslength++;
                                }
                                exfn++;
                            }
                            exfn = 0;
                            w2.ds.exfileds = new String[exfiledslength];
                            while (exfn != extempf.length) {
                                if (extempf[exfn].contains(othername)) {
                                    w2.ds.exfileds[exfm] = extempf[exfn].trim();
                                    exfm++;
                                }
                                exfn++;
                            }
                        }

                        w2.ds.table = othername;
                        int wlimit = 0;
                        if (limit != null && limit.length > 0) {
                            int limitlength = 0;
                            for (int j = 0; j != limit.length; j++) {
                                if ((limit[j].contains(othername) || (!limit[j]
                                        .contains("."))) && (limit[j] != null)) {
                                    limitlength++;
                                }
                            }
                            w2.ds.limit = new String[limitlength];
                            for (int j = 0; j != limit.length; j++) {
                                if ((limit[j].contains(othername) || (!limit[j]
                                        .contains("."))) && (limit[j] != null)) {
                                    w2.ds.limit[wlimit] = limit[j].trim();
                                    wlimit++;
                                }
                            }
                        }
                    }
                    // System.out.println(lw.get(0).getStatus());
                    leafbw.w1 = lw.get(0);
                    leafbw.getW1().ds = lw.get(0).ds;
                    ((BinWork) leafbw.w1).isleaf = 1;
                    leafbw.w1.imsql=leafbw.w1.formalSQL();
                    leafbw.w2 = new Work();
                    String fs = w2.formalSQL();
                    leafbw.w2.setImsql(fs);
                    // leafbw.w2.STATUS=w2.STATUS;
                    // System.out.println(leafbw.w1.STATUS+leafbw.w2.STATUS);
                    leafbw.w2 = w2;
                    leafbw.w2.ds = w2.ds;
                    leafbw.ds = new DataSet();
                    // System.out.println(leafbw.w1.ds.fileds.length+leafbw.w2.ds.fileds.length);
                    leafbw.ds.fileds = new String[leafbw.w1.ds.fileds.length
                            + leafbw.w2.ds.fileds.length];
                    for (int m = 0; m != leafbw.w1.ds.fileds.length; m++) {
                        leafbw.ds.fileds[m] = leafbw.w1.ds.fileds[m];
                    }
                    for (int n = 0; n != leafbw.w2.ds.fileds.length; n++) {
                        leafbw.ds.fileds[n + leafbw.w1.ds.fileds.length] = leafbw.w2.ds.fileds[n];
                    }
                    leafbw.ds.table = leafbw.w1.ds.table + leafbw.w2.ds.table;
                    int limitlength = 0;
                    if (leafbw.w1.ds.limit != null) {
                        limitlength = limitlength + leafbw.w1.ds.limit.length;
                    }
                    if (leafbw.w2.ds.limit != null) {
                        limitlength = limitlength + leafbw.w2.ds.limit.length;
                    }
                    if (limitlength != 0) {
                        leafbw.ds.limit = new String[limitlength];
                        for (int m = 0; m != leafbw.w1.ds.limit.length; m++) {
                            leafbw.ds.limit[m] = leafbw.w1.ds.limit[m];
                        }
                        for (int n = 0; n != leafbw.w2.ds.limit.length; n++) {
                            leafbw.ds.limit[n + leafbw.w1.ds.limit.length] = leafbw.w2.ds.limit[n];
                        }
                    }

                    exfiledslength = 0;
                    if (leafbw.w1.ds.exfileds != null) {
                        exfiledslength = exfiledslength
                                + leafbw.w1.ds.exfileds.length;
                    }
                    if (leafbw.w2.ds.exfileds != null) {
                        exfiledslength = exfiledslength
                                + leafbw.w2.ds.exfileds.length;
                    }
                    if (exfiledslength != 0) {
                        leafbw.ds.exfileds = new String[exfiledslength];
                        for (int m = 0; m != leafbw.w1.ds.exfileds.length; m++) {
                            leafbw.ds.exfileds[m] = leafbw.w1.ds.exfileds[m];
                        }
                        for (int n = 0; n != leafbw.w2.ds.exfileds.length; n++) {
                            leafbw.ds.exfileds[n + leafbw.w1.ds.exfileds.length] = leafbw.w2.ds.exfileds[n];
                        }
                    }

                    joinBy = addfileds;
                    if (joinBy[1].contains(othername)){
                        leafbw.joinBy[0] = joinBy[0];
                        leafbw.joinBy[1] = joinBy[1];
                    }
                    else{
                        leafbw.joinBy[0] = joinBy[1];
                        leafbw.joinBy[1] = joinBy[0];
                    }
                        
                    String jo = joinBy[0].substring(joinBy[0].indexOf(".") + 1);
                    String jo1 = joinBy[1]
                            .substring(joinBy[1].indexOf(".") + 1);
                    if (jo.length() < jo1.length())
                        leafbw.joinBy[2] = jo;
                    else
                        leafbw.joinBy[2] = jo1;
                    leafbw.imsql = sql;
                    bw.add(leafbw);
                }
                lw.remove(0);
            }
            // }
            return bw;
        }
    }

    public static LocalWork getCustomer(String fileds, String table,
            List<String> bin, String limit[], String sql, String exfileds,int isleaf) {
        int state = 0;
        if (fileds.contains("*")
                || (fileds.contains("name") && fileds.contains("rank"))) {
            state = 1;
        } else if (fileds.contains("name")) {
            if (limit != null && limit.length > 0) {
                for (int i = 0; i != limit.length; i++) {
                    if (limit[i].contains("rank")) {
                        state = 1;
                        break;
                    }
                }
            }
        } else if (fileds.contains("rank")) {
            if (limit != null && limit.length > 0) {
                for (int i = 0; i != limit.length; i++) {
                    if (limit[i].contains("name")) {
                        state = 1;
                        break;
                    }
                }
            }
        }
        if (state == 0) {
            Work w = new Work();
            w.generateId();
            w.ds = new DataSet();
            if (fileds.contains("name")) {
                w.ds.peer = pm.getPeer(0);
            } else {
                w.ds.peer = pm.getPeer(1);
            }
            w.ds.fileds = fileds.split(",");
            w.ds.table = table;
            if (exfileds != null)
                w.ds.exfileds = exfileds.split(",");
            w.ds.limit = limit;
            w.imsql = w.formalSQL();
            return w;
        } else if (state == 1) {
            Work w1 = new Work();
            Work w2 = new Work();
            w1.generateId();
            w2.generateId();
            w1.ds = new DataSet();
            w2.ds = new DataSet();
            int w1num = 0, w2num = 0, bwnum = 0;
            ;
            int filedsnum = 0;
            int w1limit = 0, w2limit = 0;
            BinWork leafbw = new BinWork();
            leafbw.ds = new DataSet();
            if (fileds.contains("*")) {
                w1.ds.fileds = new String[1];
                w2.ds.fileds = new String[1];
                w1.ds.fileds[0] = "*";
                w2.ds.fileds[0] = "*";
                leafbw.ds.fileds = new String[3];
                leafbw.ds.fileds[0] = "Customer.id";
                leafbw.ds.fileds[1] = "Customer.name";
                leafbw.ds.fileds[2] = "Customer.rank";
            } else {
                if (fileds.contains("id")) {
                    w1num++;
                    w2num++;
                    bwnum++;
                }
                if (fileds.contains("name")) {
                    w1num++;
                    bwnum++;
                }
                if (fileds.contains("rank")) {
                    w2num++;
                    bwnum++;
                }
                w1.ds.fileds = new String[w1num];
                w2.ds.fileds = new String[w2num];
                leafbw.ds.fileds = new String[bwnum];
                bwnum = 0;
                if (fileds.contains("id")) {
                    w1.ds.fileds[filedsnum] = "Customer.id";
                    w2.ds.fileds[filedsnum] = "Customer.id";
                    leafbw.ds.fileds[bwnum] = "Customer.id";
                    filedsnum++;
                    bwnum++;
                }
                if (fileds.contains("name")) {
                    w1.ds.fileds[filedsnum] = "Customer.name";
                    leafbw.ds.fileds[bwnum] = "Customer.name";
                    bwnum++;
                }
                if (fileds.contains("rank")) {
                    w1.ds.fileds[filedsnum] = "Customer.rank";
                    leafbw.ds.fileds[bwnum] = "Customer.rank";
                    bwnum++;
                }
            }
            w1.ds.peer = pm.getPeer(0);
            w2.ds.peer = pm.getPeer(1);
            w1.ds.table = table;
            w2.ds.table = table;
            if (exfileds != null) {
                w1.ds.exfileds = new String[1];
                w1.ds.exfileds[0] = "Customer.id";
                w2.ds.exfileds = new String[1];
                w2.ds.exfileds[0] = "Customer.id";
            }
            if (limit != null && limit.length > 0) {
                for (int i = 0; i != limit.length; i++) {
                    if (!limit[i].contains("name")) {
                        w2limit++;
                    }
                    if (!limit[i].contains("rank")) {
                        w1limit++;
                    }
                }
                w1.ds.limit = new String[w1limit];
                w2.ds.limit = new String[w2limit];
                w1limit = 0;
                w2limit = 0;
                for (int i = 0; i != limit.length; i++) {
                    if (!limit[i].contains("name")) {
                        w2.ds.limit[w2limit] = limit[i];
                        w2limit++;
                    }
                    if (!limit[i].contains("rank")) {
                        w1.ds.limit[w1limit] = limit[i];
                        w1limit++;
                    }
                }
            }

            leafbw.generateId();
            leafbw.w1 = new Work();
            leafbw.w1 = w1;
            leafbw.w1.ds = w1.ds;
            leafbw.w2 = new Work();
            leafbw.w2 = w2;
            leafbw.w2.ds = w2.ds;
            leafbw.ds.table = w1.ds.table;
            int limitlength = 0;
            if (w1.ds.limit != null) {
                limitlength = limitlength + w1.ds.limit.length;
            }
            if (w2.ds.limit != null) {
                limitlength = limitlength + w2.ds.limit.length;
            }
            if (limitlength != 0) {
                leafbw.ds.limit = new String[limitlength];
                for (int m = 0; m != w1.ds.limit.length; m++) {
                    leafbw.ds.limit[m] = w1.ds.limit[m];
                }
                for (int n = 0; n != w2.ds.limit.length; n++) {
                    leafbw.ds.limit[n + w1.ds.limit.length] = w2.ds.limit[n];
                }
            }

            int exfiledslength = 0;
            if (w1.ds.exfileds != null) {
                exfiledslength = exfiledslength + w1.ds.exfileds.length;
            }
            if (w2.ds.exfileds != null) {
                exfiledslength = exfiledslength + w2.ds.exfileds.length;
            }
            if (exfiledslength != 0) {
                leafbw.ds.exfileds = new String[exfiledslength];
                for (int m = 0; m != w1.ds.exfileds.length; m++) {
                    leafbw.ds.exfileds[m] = w1.ds.exfileds[m];
                }
                for (int n = 0; n != w2.ds.exfileds.length; n++) {
                    leafbw.ds.exfileds[n + w1.ds.exfileds.length] = w2.ds.exfileds[n];
                }
            }
            leafbw.joinBy[0] = "Customer.id";
            leafbw.joinBy[1] = "Customer.id";
            leafbw.joinBy[2] = "id";
            leafbw.isleaf=isleaf;
            // System.out.println(leafbw.w2.ds.fileds.length);
            leafbw.imsql = leafbw.formalSQL();
            leafbw.w1.setImsql(w1.formalSQL());
            leafbw.w2.setImsql(w2.formalSQL());
            return leafbw;
        }
        return null;
    }
}
